package com.mbc.rmt.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbc.common.dao.DAO;
import com.mbc.rmt.entities.CashRemittanceToAgentDetail;

public class CashRemittanceToAgentDetailDAOImpls extends DAO<CashRemittanceToAgentDetail> implements
		CashRemittanceToAgentDetailDAO {

	public static final String field_crtadetail_id = "crtadetailid";
	public static final String field_crtaid = "crtaid";
	public static final String field_receiptno = "receiptno";
	public static final String field_receiptid = "receiptid";
	public static final String field_receiptdetailid = "receiptdetailid";
	public static final String field_fromagentid = "fromagentid";
	public static final String field_frombranchid = "frombranchid";
	public static final String field_addcodeid = "addcodeid";
	public static final String field_sender = "sender" ;
	public static final String field_receiver = "receiver";
	public static final String field_paymentamount = "paymentamount";
	public static final String field_rate = "rate";
	public static final String field_receivedamount = "receivedamount";
	public static final String field_extrafees = "extrafees";
	public static final String field_nrc = "nrc";
	public static final String field_address = "address";
	
	public CashRemittanceToAgentDetailDAOImpls(Connection conn){
		this.conn = conn;
		this.table_name = "CashRemittanceToAgentDetails";
	}
	
	@Override
	public void create(CashRemittanceToAgentDetail entity) throws SQLException {
		query = insertParam(table_name, new String[] { field_crtadetail_id, field_crtaid, field_receiptno,
				field_receiptid, field_receiptdetailid, field_fromagentid, field_frombranchid, field_addcodeid,
				field_sender, field_receiver, field_paymentamount, field_rate, field_receivedamount, field_extrafees,
				field_nrc, field_address });
		//query = "insert into " + table_name + "( ReceiptID, ReceiptDetailID, ReceiptID_Detail, FromAgentID,FromBranchID, AddCodeID, Sender, Receiver, PaymentAmount, Rate, ReceivedAmount, ExtraFees, NRC, Address) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getCrtadetailid());
		pstmt.setLong(i++, entity.getCrtaid());
		pstmt.setString(i++, entity.getReceiptNo());
		pstmt.setLong(i++, entity.getReceiptID());
		pstmt.setLong(i++, entity.getReceiptID_Detail());
		if(entity.getFromAgentID()!=null)
			pstmt.setLong(i++, entity.getFromAgentID());
		else
			pstmt.setString(i++, null);
		if(entity.getFromBranchID()!=null)
			pstmt.setLong(i++, entity.getFromBranchID());
		else
			pstmt.setString(i++, null);
		pstmt.setLong(i++, entity.getAddCodeID());
		pstmt.setString(i++, entity.getSender());
		pstmt.setString(i++, entity.getReceiver());
		pstmt.setDouble(i++, entity.getPaymentAmount());
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setDouble(i++, entity.getReceivedAmount());
		pstmt.setDouble(i++, entity.getExtraFee());
		pstmt.setString(i++, entity.getNrc());
		pstmt.setString(i++, entity.getAddress());
		
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where ReceiptDetailID = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	
	}

	@Override
	public ArrayList<CashRemittanceToAgentDetail> findByHeaderReceiptID(
			long headerKey) throws SQLException {
		ArrayList<CashRemittanceToAgentDetail> l_list = new ArrayList<CashRemittanceToAgentDetail>();
		query = " select crd.*,(case when crd.FromAgentID is null then '' else a.agent_code end)agent_code,"
				+ "(case when crd.FromAgentID is null then b.BranchCode else '' end) BranchCode,ac.Code,crar.ReceiptNo " 
				+ " from CashRemittanceToAgentDetails crd "
				+ " left join CashRemittanceByAgent_Archive crar on crar.ReceiptID = crd.ReceiptID "
				+ " left join Agent a on a.agent_id = crd.FromAgentID "
				+ " left join Branch b on b.BranchId = crd.FromBranchID "
				+ " left join AddCode ac on ac.AddCodeId = crd.AddCodeID "
				+ " where crd.CRTAID = " + headerKey;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		
		if(rs.next()) {
			do {
				l_list.add(readRecord(rs));
			} while(rs.next());
		}
		rs.close();
		pstmt.close();
		return l_list;
	}

	@Override
	public void deleteByHeaderReceiptID(long headerKey) throws SQLException {
		query = getDeleteQuery() + " where CRTAID = " + headerKey;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	protected CashRemittanceToAgentDetail readRecord(ResultSet rs)
			throws SQLException {
		CashRemittanceToAgentDetail l_entity = new CashRemittanceToAgentDetail();
		l_entity.setCrtadetailid(rs.getLong("CRTADetailID"));
		l_entity.setCrtaid(rs.getLong("CRTAID"));
		l_entity.setReceiptNo(rs.getString("ReceiptNo"));
		l_entity.setReceiptID(rs.getLong("ReceiptID"));
		l_entity.setReceiptDetailID(rs.getLong("ReceiptDetailID"));
		l_entity.setFromAgentID(rs.getLong("FromAgentID"));
		l_entity.setFromAgentCode(rs.getString("agent_code"));
		l_entity.setFromBranchID(rs.getLong("FromBranchID"));
		l_entity.setFromBranchCode(rs.getString("BranchCode"));
		l_entity.setAddCodeID(rs.getLong("AddCodeID"));
		l_entity.setAddCodeC(rs.getString("Code"));
		l_entity.setSender(rs.getString("Sender"));
		l_entity.setReceiver(rs.getString("Receiver"));
		l_entity.setPaymentAmount(rs.getDouble("PaymentAmount"));
		l_entity.setRate(rs.getDouble("Rate"));
		l_entity.setReceivedAmount(rs.getDouble("ReceivedAmount"));
		l_entity.setExtraFee(rs.getDouble("ExtraFees"));
		l_entity.setNrc(rs.getString("NRC"));
		l_entity.setAddress(rs.getString("Address"));
		return l_entity;
	}

	@Override
	public ArrayList<CashRemittanceToAgentDetail> loadConfirmed(String agentCode, long paymentCurrencyID, long receivedCurrencyId)
			throws SQLException {
		ArrayList<CashRemittanceToAgentDetail> l_list = new ArrayList<CashRemittanceToAgentDetail>();
		query = "SELECT 0 as ReceiptID, a.ReceiptNo AS ReceiptDetailNo, a.FromAgentID, c.agent_code AS FromAgentCode, a.DefaultBranchID as FromBranchID, e.BranchCode as FromBranchCode, b.AddCodeID, d.Code as AddCodeC, b.Sender, b.Receiver, b.PaymentAmount, a.Rate, b.ReceivedAmount, b.ExtraFees, b.NRC, b.Address  FROM CashRemittanceByAgent a " +
				"INNER JOIN CashRemittanceByAgentDetails b ON a.ReceiptID = b.ReceiptID " +
				"INNER JOIN Agent c ON c.agent_code = '' " +
				"INNER JOIN AddCode d ON b.AddCodeID = d.AddCodeId " +
				"INNER JOIN Branch e ON e.BranchId = a.DefaultBranchID" +
				"WHERE b.Status = 0";
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next()){
			l_list.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_list;
	}

	@Override
	public CashRemittanceToAgentDetail find(long key) throws SQLException {
		CashRemittanceToAgentDetail l_info = null;
		query = getSelectQuery() + " where ReceiptDetailID = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_info = readRecord(rs);
			} while (rs.next());
		}
		rs.close();
		pstmt.close();

		return l_info;
	}

}
